-- JIFEN ADD 2013.06.22     ---
-- FOR INSURANCE APPLICATION --

CREATE TABLE [dbo].[APPLICATION_INSURANCE](
	[applicationID] [int] NOT NULL,
	[custID] [nvarchar](50) NULL,
	[applicationStatus] [nvarchar](50) NOT NULL,
	[accountNo] [nvarchar](50) NULL,
	[applicationDate] [date] NULL,
	[startDate]  [date] NULL,
	[endDate]  [date] NULL,
	[paymentWay]  [nvarchar](50) NULL,
	[approvalDate] [date] NULL,
	[staffID] [nvarchar](50) NULL,
	[savingAccountNo] [nvarchar](50) NULL,
	[applyCategory] [nvarchar](50) NULL,
	[applyProduct] [nvarchar](50) NULL,
	[productType] [nvarchar](50) NULL,
 CONSTRAINT [PK_APPLICATION_INSURANCE] PRIMARY KEY CLUSTERED 
(
	[applicationID] ASC,
	[applicationStatus] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

CREATE TABLE [dbo].[APPLICATION_INSURANCE_INSURED](
	[insuredId] [int] NOT NULL,
	[parentinsuredId] [int] NULL,
	[applicationInsuranceId] [int] NULL,
	[fName] [nvarchar](50) NULL,
	[lName] [nvarchar](50) NULL,
	[NRIC] [nvarchar](50) NULL,
	[nationality] [nvarchar](50) NULL,
	[gender] [nvarchar](1) NULL,
	[marital] [nvarchar](1) NULL,
	[birth] [date] NULL,
	[peopleType] [nvarchar](50) NULL,
	[planType] [nvarchar](50) NULL,
	[occupation] [nvarchar](50) NULL,
	[telphone] [nvarchar](50) NULL,
	[longStay] [nvarchar](50) NULL,
	[mobile] [nvarchar](50) NULL,
	[email] [nvarchar](50) NULL,
	[address] [nvarchar](50) NULL,
	[postalCode] [nvarchar](50) NULL,
	[employerName] [nvarchar](256) NULL,
	[employerAddress] [nvarchar](256) NULL,
	[business] [nvarchar](256) NULL,
	[qstDisease] [nvarchar](256) NULL,
	[illness] [nvarchar](50) NULL,
	[illDuration] [nvarchar](50) NULL,
	[illTreatment] [nvarchar](50) NULL,
	[illhospital] [nvarchar](500) NULL,
	[otherInsurance] [nvarchar](500) NULL,
	[foreseeable] [nvarchar](500) NULL,
	[bloodPressureIllSystolic] [nvarchar](50) NULL,
	[bloodPressureIllDate] [nvarchar](50) NULL,
	[bloodPressureIllHospital] [nvarchar](500) NULL,
	[HyperlipidaemiaLevel] [nvarchar](50) NULL,
	[HyperlipidaemiaDate] [nvarchar](50) NULL,
	[HyperlipidaemiaHospital] [nvarchar](500) NULL,
 CONSTRAINT [PK_APPLICATION_INSURANCE_INSURED] PRIMARY KEY CLUSTERED 
(
	[insuredId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[INSURED](
insuredId int,
parentinsuredId	int,
applicationInsuranceId	int,
fName	nvarchar(50),
lName	nvarchar(50),
NRIC	nvarchar(50),
nationality	nvarchar(50),
gender	nvarchar(1),
marital	nvarchar(1),
birth	date,
peopleType nvarchar(50),
planType nvarchar(50),
occupation	nvarchar(50),
telphone	nvarchar(50),
longStay	nvarchar(50),
mobile	nvarchar(50),
email	nvarchar(50),
address	nvarchar(50),
postalCode	nvarchar(50),
employerName nvarchar(256),
employerAddress nvarchar(256),	
business nvarchar(256),	
qstDisease nvarchar(256),
illness nvarchar(50),
illDuration nvarchar(50),
illTreatment nvarchar(50),
illhospital nvarchar(500),
otherInsurance nvarchar(500),
foreseeable  nvarchar(500),
bloodPressureIllSystolic   nvarchar(50),
bloodPressureIllDate   nvarchar(50),
bloodPressureIllHospital  nvarchar(500),
HyperlipidaemiaLevel   nvarchar(50),
HyperlipidaemiaDate   nvarchar(50),
HyperlipidaemiaHospital  nvarchar(500)
 CONSTRAINT [PK_INSURED] PRIMARY KEY CLUSTERED 
(
	[insuredID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];




